package dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import entity.Student;
import util.DBUtil;
import util.StringUtil;

public class StudentDao implements StudentDaoI {
	public static int PAGE_SIZE = 10;

	@Override
	public int add(Student st) {

		return 0;
	}

	@Override
	public int update(Student st) {
		Connection con = DBUtil.getConnection();// 调用get方法创建连接
		PreparedStatement ppst = null;// 创建preparedstatement来执行sql语句
		String sql = "update s_student set sname = ?,sex = ?,telephone = ?,qq = ?,"
				+ "wechat = ?,gradeid = ?,enterdate = ?,fromcity = ?,birthday = ?,address = ?,pass = ?  where stuno = ?";
		int i = 0;
		try {
			ppst = con.prepareStatement(sql);
			ppst.setString(1, st.getSname());
			ppst.setString(2, st.getSex());
			ppst.setString(3, st.getTelephone());
			ppst.setString(4, st.getQq());
			ppst.setString(5, st.getWechat());
			ppst.setLong(6, st.getGradeId());
			ppst.setDate(7, new Date(st.getEnterDate().getTime()));
			ppst.setString(8, st.getFromcity());
			ppst.setDate(9, new Date(st.getBirthday().getTime()));
			ppst.setString(10, st.getAddress());
			ppst.setString(11, st.getPass());
			ppst.setLong(12, st.getStuno());
			i = ppst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(ppst);
			DBUtil.close(con);
		}
		return i;

	}

	// 根据学号删除学生
	@Override
	public int del(long stuno) {
		Connection con = DBUtil.getConnection();
		PreparedStatement ppst = null;
		String sql = "delete from s_student where stuno = ?";
		int rows = 0;
		try {
			ppst = con.prepareStatement(sql);
			ppst.setLong(1, stuno);
			rows = ppst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(ppst);
			DBUtil.close(con);
		}
		return rows;

	}

	// R:分为两种：1：查单个对象；
	@Override
	public Student get(long stuno) {
		Connection con = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Student stu = null;
		String sql = "select * from s_student where stuno=?";
		try {
			ps = con.prepareStatement(sql);
			ps.setLong(1, stuno);
			rs = ps.executeQuery();
			if (rs.next()) {
				stu = new Student();
				stu.setStuno(rs.getLong("stuno"));
				stu.setSname(rs.getString("sname"));
				stu.setSex(rs.getString("sex"));
				stu.setGradeId(rs.getLong("gradeid"));

				stu.setEnterDate(rs.getDate("enterdate"));
				stu.setTelephone(rs.getString("telephone"));
				stu.setQq(rs.getString("qq"));
				stu.setWechat(rs.getString("wechat"));
				stu.setFromcity(rs.getString("fromcity"));
				stu.setBirthday(rs.getDate("birthday"));
				stu.setAddress(rs.getString("address"));
				stu.setPass(rs.getString("pass"));

			}
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} finally {
			DBUtil.close(ps);
			DBUtil.close(con);
		}
		return stu;
	}

	// 2:查全部
	@Override
	public List<Student> searchAll() {
		String sql = "select * from s_student order by stuno asc";

		List<Student> datas = new ArrayList<>();
		Connection con = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Student stu = null;
		try {
			ps = con.prepareStatement(sql);

			rs = ps.executeQuery();
			while (rs.next()) {
				stu = new Student();
				stu.setStuno(rs.getLong("stuno"));
				stu.setSname(rs.getString("sname"));
				stu.setAddress(rs.getString("address"));
				stu.setBirthday(rs.getDate("birthday"));
				stu.setGradeId(rs.getLong("gradeid"));
				stu.setTelephone(rs.getString("telephone"));
				datas.add(stu);
			}

		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(ps);
			DBUtil.close(con);
		}
		return datas;

	}

	// 分页,count(*)
	/**
	 * 
	 * @param pageSize
	 *            页大小
	 * @param pageNo
	 *            页号
	 * @return
	 */
	@Override
	public List<Student> paging(int pageSize, int pageNo) {

		String sql = "select * from s_student order by stuno asc limit ?,? ";

		List<Student> datas = new ArrayList<>();
		Connection con = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Student stu = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, pageSize * (pageNo - 1));
			ps.setInt(2, pageSize);
			rs = ps.executeQuery();
			while (rs.next()) {
				stu = new Student();
				stu.setStuno(rs.getLong("stuno"));
				stu.setSname(rs.getString("sname"));
				stu.setAddress(rs.getString("address"));
				stu.setBirthday(rs.getDate("birthday"));
				stu.setGradeId(rs.getLong("gradeid"));
				stu.setTelephone(rs.getString("telephone"));

				datas.add(stu);
			}

		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(ps);
			DBUtil.close(con);
		}
		return datas;

	}

	@Override
	public List<Student> paging(int pageNo) {

		String sql = "select * from s_student order by stuno asc limit ?,? ";

		List<Student> datas = new ArrayList<>();
		Connection con = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Student stu = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, PAGE_SIZE * (pageNo - 1));
			ps.setInt(2, PAGE_SIZE);
			rs = ps.executeQuery();
			while (rs.next()) {
				stu = new Student();
				stu.setStuno(rs.getLong("stuno"));
				stu.setSname(rs.getString("sname"));
				stu.setAddress(rs.getString("address"));
				stu.setBirthday(rs.getDate("birthday"));
				stu.setGradeId(rs.getLong("gradeid"));
				stu.setTelephone(rs.getString("telephone"));

				datas.add(stu);
			}

		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(ps);
			DBUtil.close(con);
		}
		return datas;

	}
	// 有问题：不好区分找没找到
	// public Student get(long stuno) {
	// Connection con = DBUtil.getConnection();
	// PreparedStatement ps = null;
	// ResultSet rs = null;
	// Student stu = new Student();
	// String sql = "select * from s_student where stuno=?";
	// try {
	// ps = con.prepareStatement(sql);
	// ps.setLong(1, stuno);
	// rs = ps.executeQuery();
	// if (rs.next()) {
	// stu.setStuno(rs.getLong("stuno"));
	// stu.setSname(rs.getString("sname"));
	// stu.setAddress(rs.getString("address"));
	// }
	// } catch (SQLException e) {
	// // TODO 自动生成的 catch 块
	// e.printStackTrace();
	// } finally {
	// DBUtil.close(ps);
	// DBUtil.close(con);
	// }
	// return stu;
	// }

	public Student login(long stun, String p) {
		String sql = "select * from s_student where stuno=? and pass=?";

		Connection con = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Student stu = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setLong(1, stun);
			ps.setString(2, p);
			rs = ps.executeQuery();
			if (rs.next()) {
				stu = new Student();
				stu.setStuno(rs.getLong("stuno"));
				stu.setSname(rs.getString("sname"));
				stu.setAddress(rs.getString("address"));
				stu.setBirthday(rs.getDate("birthday"));
				stu.setSex(rs.getString("sex"));
				stu.setTelephone(rs.getString("telephone"));
				stu.setQq(rs.getString("qq"));
				stu.setWechat(rs.getString("wechat"));
				stu.setGradeId(rs.getLong("gradeId"));
				stu.setEnterDate(rs.getDate("enterDate"));
				stu.setFromcity(rs.getString("fromcity"));
				stu.setPass(rs.getString("pass"));

				return stu;
			}

		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(ps);
			DBUtil.close(con);
		}
		return null;

	}

	// 获得总页数
	@Override
	public int getTotalPage() {
		String sql = "select count(*) from s_student";

		Connection con = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Student stu = null;
		int a = 0;
		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs.next()) {
				a = rs.getInt(1);
			}

		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(ps);
			DBUtil.close(con);
		}
		// int page = (int) Math.ceil(a * 1.0 / PAGE_SIZE);
		int page = a / PAGE_SIZE + (a % PAGE_SIZE == 0 ? 0 : 1);
		return page;

	}

	@Override
	public boolean checkTele(String no) {

		String sql = "select count(*) from s_student  where telephone=?";

		Connection con = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, no);
			rs = ps.executeQuery();
			if (rs.next()) {
				int c = rs.getInt(1);
				return c > 0;
			}

		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(ps);
			DBUtil.close(con);
		}
		return false;
	}

	// 多条件查询
	// null 不考虑
	@Override
	public List<Student> paging(int pageNo, String name, String tele, String qq) {

		String sql = "select * from s_student where 1=1";
		// 存放参数
		List<Object> params = new ArrayList<>();

		if (!StringUtil.isBank(name)) {
			sql += " and sname like ?";
			params.add("%" + name + "%");
		}
		if (!StringUtil.isBank(tele)) {
			sql += " and telephone like ?";
			params.add("%" + tele + "%");
		}
		if (!StringUtil.isBank(qq)) {
			sql += " and qq like ?";
			params.add("%" + qq + "%");
		}

		sql += " order by stuno asc limit ?,? ";
		System.out.println(sql);
		params.add(PAGE_SIZE * (pageNo - 1));
		params.add(PAGE_SIZE);
		List<Student> datas = new ArrayList<>();
		Connection con = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Student stu = null;
		try {
			ps = con.prepareStatement(sql);
			for (int i = 0; i < params.size(); i++) {
				ps.setObject(i + 1, params.get(i));
			}

			// int index = 1;
			// ps.setString(index++, "%" + name + "%");
			// ps.setString(index++, tele);
			// ps.setString(index++, qq);
			// ps.setInt(index++, PAGE_SIZE * (pageNo - 1));
			// ps.setInt(index++, PAGE_SIZE);
			rs = ps.executeQuery();
			while (rs.next()) {
				stu = new Student();
				stu.setStuno(rs.getLong("stuno"));
				stu.setSname(rs.getString("sname"));
				stu.setAddress(rs.getString("address"));
				stu.setBirthday(rs.getDate("birthday"));
				stu.setGradeId(rs.getLong("gradeid"));
				stu.setTelephone(rs.getString("telephone"));
				stu.setQq(rs.getString("qq"));
				datas.add(stu);
			}

		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(ps);
			DBUtil.close(con);
		}
		return datas;

	}

	@Override
	public int getTotalPage(String name, String tele, String qq) {

		String sql = "select count(*) from s_student where 1=1";
		List<Object> params = new ArrayList<>();

		if (!StringUtil.isBank(name)) {
			sql += " and sname like ?";
			params.add("%" + name + "%");
		}
		if (!StringUtil.isBank(tele)) {
			sql += " and telephone like ?";
			params.add("%" + tele + "%");
		}
		if (!StringUtil.isBank(qq)) {
			sql += " and qq like ?";
			params.add("%" + qq + "%");
		}

		Connection con = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Student stu = null;
		int a = 0;
		try {
			ps = con.prepareStatement(sql);
			for (int i = 0; i < params.size(); i++) {
				ps.setObject(i + 1, params.get(i));
			}
			rs = ps.executeQuery();
			if (rs.next()) {
				a = rs.getInt(1);
			}

		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(ps);
			DBUtil.close(con);
		}
		// int page = (int) Math.ceil(a * 1.0 / PAGE_SIZE);
		int page = a / PAGE_SIZE + (a % PAGE_SIZE == 0 ? 0 : 1);
		return page;

	}

}
